package com.ham.facade.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ham.common.AppUtil;
import com.ham.common.DBConn;
import com.ham.dto.ItemDTO;
import com.ham.facade.MainFacade;
import com.ham.model.ItemModel;
import com.ham.model.ItemRatingModel;

public class MainFacadeImpl implements MainFacade {

	private static final Logger LOGGER = LoggerFactory
			.getLogger(MainFacadeImpl.class);

	@Override
	public List<ItemDTO> populateDataForReportFromFile(Date ratingDate)
			throws SQLException {
		DateTime dateEnd = new DateTime(ratingDate);
		DateTime dateStart = dateEnd.minusMonths(3);
		String ratingDateA = AppUtil.formatDate(dateStart.toDate()).toString();
		String ratingDateB = AppUtil.formatDate(dateEnd.toDate()).toString();

		Connection conn = DBConn.getInstance().connect();
		ResultSet rsItem = conn.prepareStatement("SELECT * FROM ITEM_TMP")
				.executeQuery();
		List<ItemDTO> items = new ArrayList<ItemDTO>();
		double multiply = 0;
		double totalPick = 0;

		while (rsItem.next()) {
			int itemId = rsItem.getInt(1);
			ItemDTO dto = new ItemDTO();
			dto.setId(itemId);
			dto.setName(rsItem.getString(2));
			dto.setPurchaseDate(rsItem.getDate(3));

			for (int i = 1; i <= 5; i++) {
				PreparedStatement ps = conn
						.prepareStatement("SELECT count(*) FROM ITEM_RATING_TMP WHERE ITEM_ID_=? AND RATING_=? AND rating_date_ >= str_to_date(?, '%Y-%m-%d') AND  rating_date_ <= str_to_date(?, '%Y-%m-%d')");
				ps.setInt(1, itemId);
				ps.setInt(2, i);
				ps.setString(3, ratingDateA);
				ps.setString(4, ratingDateB);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					int temp = rs.getInt(1);
					totalPick = totalPick + temp;
					multiply = multiply + (i * temp);
					temp = 0;
				}
			}
			dto.setAverageRating(multiply / totalPick);
			dto.setRatingDateA(dateStart.toDate());
			dto.setRatingDateZ(dateEnd.toDate());
			items.add(dto);
		}
		conn.close();

		return items;
	}

	@Override
	public List<ItemDTO> populateDataForReport(Date ratingDate)
			throws SQLException {

		DateTime dateEnd = new DateTime(ratingDate);
		DateTime dateStart = dateEnd.minusMonths(3);
		String ratingDateA = AppUtil.formatDate(dateStart.toDate()).toString();
		String ratingDateB = AppUtil.formatDate(dateEnd.toDate()).toString();

		Connection conn = DBConn.getInstance().connect();
		ResultSet rsItem = conn.prepareStatement("SELECT * FROM ITEM")
				.executeQuery();
		List<ItemDTO> items = new ArrayList<ItemDTO>();
		double multiply = 0;
		double totalPick = 0;

		while (rsItem.next()) {
			int itemId = rsItem.getInt(1);
			ItemDTO dto = new ItemDTO();
			dto.setId(itemId);
			dto.setName(rsItem.getString(2));
			dto.setPurchaseDate(rsItem.getDate(3));

			for (int i = 1; i <= 5; i++) {
				PreparedStatement ps = conn
						.prepareStatement("SELECT count(*) FROM ITEM_RATING WHERE ITEM_ID_=? AND RATING_=? AND rating_date_ >= str_to_date(?, '%Y-%m-%d') AND  rating_date_ <= str_to_date(?, '%Y-%m-%d')");
				ps.setInt(1, itemId);
				ps.setInt(2, i);
				ps.setString(3, ratingDateA);
				ps.setString(4, ratingDateB);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					int temp = rs.getInt(1);
					totalPick = totalPick + temp;
					multiply = multiply + (i * temp);
					temp = 0;
				}
			}

			dto.setAverageRating(multiply / totalPick);
			dto.setRatingDateA(dateStart.toDate());
			dto.setRatingDateZ(dateEnd.toDate());
			items.add(dto);
		}
		conn.close();

		return items;
	}

	@Override
	@Deprecated
	public void importItems(List<ItemModel> items, boolean truncateTableFirst)
			throws SQLException {
		Connection conn = DBConn.getInstance().connect();
		if (truncateTableFirst) {
			conn.prepareStatement("TRUNCATE TABLE " + ItemModel.TABLE_NAME)
					.executeUpdate();

		}
		for (ItemModel itemModel : items) {
			conn.prepareStatement(itemModel.getInsertStatement())
					.executeUpdate();
		}
		conn.close();
	}

	@Override
	public void importItems(List<ItemModel> items) throws SQLException {
		Connection conn = DBConn.getInstance().connect();
		conn.prepareStatement("TRUNCATE TABLE " + ItemModel.TABLE_NAME)
				.executeUpdate();
		for (ItemModel itemModel : items) {
			conn.prepareStatement(itemModel.getInsertStatement())
					.executeUpdate();
		}
		conn.close();
	}

	@Override
	public void importItemRatings(List<ItemRatingModel> itemRatings)
			throws SQLException {
		Connection conn = DBConn.getInstance().connect();
		conn.prepareStatement("TRUNCATE TABLE " + ItemRatingModel.TABLE_NAME)
				.executeUpdate();
		for (ItemRatingModel rating : itemRatings) {
			conn.prepareStatement(rating.getInsertStatement()).executeUpdate();
		}
		conn.close();

	}

}
